********************************************************************************
** 	TITLE: b11_2_probation_violation.do
**
**	PROJECT: IGNITE
** 
**  PURPOSE: Cleans alternative measures of recidivism
**	(1) Probation Violation measures from Genesee County Jail 
** 
**	Note: used for Appendix Table I, Secondary Alternative Specifications.
********************************************************************************

set sortseed 13	

/********************************************************************************
**	(1) Probation Violation
********************************************************************************/
******Cleaning The data and saving tempfile

//Genesee County JMS Booking Data
import delimited "$input_data/jms_all_22dec2022/inmates_bybookdate.csv", clear
		
		ren *1 *
		ren bookingstarttime booking_time
		
		keep inmate booking_time charge

//Probation Violation as charge at Booking		
		gen probat_vi = (strpos(charge,"Probation") > 0)
		
		
		foreach v in booking_time{
			gen `v'_orig = `v'
			drop `v'
			gen `v' = clock(`v'_orig,"MDYhm")
			format `v' %tc
			drop `v'_orig
			local n="booking"
			gen `n'_date = dofc(`v')
			format `n'_date %td
			
			
		
			gen `n'_year = year(`n'_date)
			gen `n'_month = month(`n'_date)
		}
		
		duplicates drop inmate booking_date, force
		drop booking_time
		keep if yofd(booking_date)>=2015
		tempfile old
		save `old', replace

//Genesee County JMS Booking Data (May 2023 Refresh)
	import excel "$input_data/jms_refresh_10may2023/Inmates By Book Date - All Arrests.xls", clear firstrow allstring cellrange(B6:H21278)
	
	ren *, lower()
	replace inmate=inmate[_n-1] if mi(inmate)
	split bookingstarttime, p(" ")
	
	gen booking_date = date(bookingstarttime1,"DMY")
	format %td booking_date
	duplicates drop inmate booking_date, force

	bys inmate (booking_date): gen id=_n
	drop if id!=1
	keep inmate booking_date charge

//Probation Violation as charge at Booking			
	gen probat_vi = (strpos(charge,"Probation") > 0)
	
	append using `old'
	
	sort inmate booking_date
	
	duplicates drop inmate booking_date, force

//Keeping inmate-by-bookingdate and whether booking was due to probation violation	
	keep inmate booking_date probat_vi
		
	save "$output_data/probation_violation.dta", replace
